USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_datename2no') BEGIN
	DROP  FUNCTION  tsql.fn_datename2no
	PRINT '<<< DROP FUNCTION tsql.fn_datename2no - Completed with SUCCESS >>>'
END
GO


/*================================================================================================
Name:			tsql.fn_datename2no
Type:			User defined function 
Owner:			
Description:	Returns the month number from month name 
Parameters:		@date = date to ge tthe last day for
Dependens on:  	none
Usage:			SELECT [Lib].[tsql].[fn_datename2no] (GETDATE())
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_datename2no&referringTitle=Documentation		
Version:		20121025
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		10/24/2012 - Adrian E Dudau - Function created
History:		10/25/2012 - Adrian E Dudau - Added @@DATEFIRST logic
================================================================================================*/

CREATE FUNCTION tsql.fn_datename2no (@name NVARCHAR(50))
RETURNS SMALLINT
AS

BEGIN
	SET @name = UPPER(LTRIM(RTRIM(ISNULL(@name,''))))

	RETURN (CASE WHEN @name IN('JAN','JANUARY') THEN 1 
				WHEN @name IN('FEB','FEBRUARY') THEN 2 
				WHEN @name IN('MAR','MARCH') THEN 3 
				WHEN @name IN('APR','APRIL') THEN 4 
				WHEN @name IN('MAY') THEN 5 
				WHEN @name IN('JUN','JUNE') THEN 6 
				WHEN @name IN('JUL','JULY') THEN 7 
				WHEN @name IN('AUG','AUGUST') THEN 8 
				WHEN @name IN('SEP','SEPTEMBER') THEN 9 
				WHEN @name IN('OCT','OCTOBER') THEN 10 
				WHEN @name IN('NOV','NOVEMBER') THEN 11 
				WHEN @name IN('DEC','DECEMBER') THEN 12 
				
				WHEN @name IN('MON','MONDAY') THEN (DATEPART(DW,'01/01/1900') + @@DATEFIRST) % 7
				WHEN @name IN('TUE','TUESDAY') THEN (DATEPART(DW,'01/02/1900') + @@DATEFIRST) % 7
				WHEN @name IN('WED','WEDNESDAY') THEN (DATEPART(DW,'03/07/1900') + @@DATEFIRST) % 7
				WHEN @name IN('THU','THURSDAY') THEN (DATEPART(DW,'01/04/1900') + @@DATEFIRST) % 7
				WHEN @name IN('FRI','FRIDAY') THEN(DATEPART(DW,'01/05/1900') + @@DATEFIRST) % 7
				WHEN @name IN('SAT','SATURDAY') THEN (DATEPART(DW,'01/06/1900') + @@DATEFIRST) % 7
				WHEN @name IN('SUN','SUNDAY') THEN (DATEPART(DW,'01/07/1900') + @@DATEFIRST) % 7 
				
				ELSE NULL
			END)	
END

GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_datename2no') BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_datename2no - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_datename2no - Completed with ERROR >>>'
END


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

-- GRANT SELECT TO PUBLIC will be used at Db level



